
/*********************
 **  CreateTime: 2006-7-26 
 **********************/

using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;

using EntLibEC.Cmn;
using EntLibEC.Info.PIM;


namespace EntLibEC.Dac.PIM
{
    /// <summary>
    /// Contains the the data access methods for ProductDescriptionDA.
    /// </summary>
    public abstract class ProductDescriptionDA
    {
        #region Sql Script

        private const string SQL_GET_ALL_PRODUCTDESCRIPTION
            = @"SELECT a.ProductSysNo,a.Title,a.Advertise,a.AdvertiseColor,a.Bullet,a.WebDescription,a.LineDescription,a.DetailSpecification,a.LastEditUserSysNo,a.LastEditTime,b.UserName as LastEditUsername  
				FROM PIM_ProductDescription (NOLOCK)
                LEFT JOIN Ent_Sys_User b (NOLOCK) ON a.LastEditUserSysNo = b.SysNo";

        private const string SQL_GET_PRODUCTDESCRIPTION
            = @"SELECT a.ProductSysNo,a.Title,a.Advertise,a.AdvertiseColor,a.Bullet,a.WebDescription,a.LineDescription,a.DetailSpecification,a.LastEditUserSysNo,a.LastEditTime,b.UserName as LastEditUsername  
				FROM PIM_ProductDescription (NOLOCK)
                LEFT JOIN Ent_Sys_User b (NOLOCK) ON a.LastEditUserSysNo = b.SysNo
				WHERE ProductSysNo=@ProductSysNo";

        private const string SQL_INSERT_PRODUCTDESCRIPTION = "INSERT INTO PIM_ProductDescription(Title,Advertise,AdvertiseColor,Bullet,WebDescription,LineDescription,DetailSpecification,LastEditUserSysNo,LastEditTime) VALUES (@Title,@Advertise,@AdvertiseColor,@Bullet,@WebDescription,@LineDescription,@DetailSpecification,@LastEditUserSysNo,GETDATE()) SELECT @@IDENTITY";
        private const string SQL_UPDATE_PRODUCTDESCRIPTION = "UPDATE PIM_ProductDescription SET Title=@Title,Advertise=@Advertise,AdvertiseColor=@AdvertiseColor,Bullet=@Bullet,WebDescription=@WebDescription,LineDescription=@LineDescription,DetailSpecification=@DetailSpecification,LastEditUserSysNo=@LastEditUserSysNo,LastEditTime=GETDATE() WHERE ProductSysNo=@ProductSysNo";
        private const string SQL_DELETE_PRODUCTDESCRIPTION = "DELETE FROM PIM_ProductDescription WHERE ProductSysNo = @ProductSysNo";
        #endregion

        protected static ProductDescriptionInfo Map(DataRow row)
        {
            ProductDescriptionInfo info = new ProductDescriptionInfo();

            info.ProductSysNo = Convert.ToInt32(row["ProductSysNo"]);
            info.Title = row["Title"].ToString();
            info.Advertise = row["Advertise"].ToString();
            info.AdvertiseColor = row["AdvertiseColor"].ToString();
            info.Bullet = row["Bullet"].ToString();
            info.WebDescription = row["WebDescription"].ToString();
            info.LineDescription = row["LineDescription"].ToString();
            info.DetailSpecification = row["DetailSpecification"].ToString();
            info.LastEditUserSysNo = Convert.ToInt32(row["LastEditUserSysNo"]);
            info.LastEditTime = Convert.ToDateTime(row["LastEditTime"]);
            info.LastEditUsername = row["LastEditUsername"].ToString();
            return info;
        }

        public static ProductDescriptionInfo GetProductDescription(int ProductSysNo)
        {
            SqlParameter[] parms = new SqlParameter[]{
                new SqlParameter("@ProductSysNo", SqlDbType.Int)
            };
            parms[0].Value = ProductSysNo;

            DataTable dt = DataAccessFacade.GetDataTable(SQL_GET_PRODUCTDESCRIPTION, parms);
            if (dt == null || dt.Rows.Count != 1)
                return null;

            return Map(dt.Rows[0]);
        }

        public static int InsertProductDescription(ProductDescriptionInfo info)
        {
            SqlParameter[] parms = new SqlParameter[]{
                new SqlParameter("@ProductSysNo", SqlDbType.Int),
                new SqlParameter("@Title", SqlDbType.NVarChar, 100),
                new SqlParameter("@Advertise", SqlDbType.NVarChar, 100),
                new SqlParameter("@AdvertiseColor", SqlDbType.Char,6),
                new SqlParameter("@Bullet", SqlDbType.NVarChar,500),
                new SqlParameter("@WebDescription", SqlDbType.NVarChar, 100),
                new SqlParameter("@LineDescription", SqlDbType.NVarChar, 100),
                new SqlParameter("@DetailSpecification", SqlDbType.NText),
                new SqlParameter("@LastEditUserSysNo", SqlDbType.Int),
                new SqlParameter("@LastEditTime", SqlDbType.DateTime)
             };

            parms[0].Value = info.ProductSysNo;
            parms[1].Value = info.Title;
            parms[2].Value = info.Advertise;
            parms[3].Value = info.AdvertiseColor;
            parms[4].Value = info.Bullet;
            parms[5].Value = info.WebDescription;
            parms[6].Value = info.LineDescription;
            parms[7].Value = info.DetailSpecification;
            parms[8].Value = info.LastEditUserSysNo;
            parms[9].Value = info.LastEditTime;

            object obj = DataAccessFacade.ExecScalarSqlCommand(SQL_INSERT_PRODUCTDESCRIPTION, parms);
            return Convert.ToInt32(obj);
        }

        public static void UpdateProductDescription(ProductDescriptionInfo info)
        {
            SqlParameter[] parms = new SqlParameter[]{
                new SqlParameter("@ProductSysNo", SqlDbType.Int),
                new SqlParameter("@Title", SqlDbType.NVarChar, 100),
                new SqlParameter("@Advertise", SqlDbType.NVarChar, 100),
                new SqlParameter("@AdvertiseColor", SqlDbType.Char, 6),
                new SqlParameter("@Bullet", SqlDbType.NVarChar, 500),
                new SqlParameter("@WebDescription", SqlDbType.NVarChar, 100),
                new SqlParameter("@LineDescription", SqlDbType.NVarChar, 100),
                new SqlParameter("@DetailSpecification", SqlDbType.NText),
                new SqlParameter("@LastEditUserSysNo", SqlDbType.Int),
                new SqlParameter("@LastEditTime", SqlDbType.DateTime)
            };

            parms[0].Value = info.ProductSysNo;
            parms[1].Value = info.Title;
            parms[2].Value = info.Advertise;
            parms[3].Value = info.AdvertiseColor;
            parms[4].Value = info.Bullet;
            parms[5].Value = info.WebDescription;
            parms[6].Value = info.LineDescription;
            parms[7].Value = info.DetailSpecification;
            parms[8].Value = info.LastEditUserSysNo;
            parms[9].Value = info.LastEditTime;

            DataAccessFacade.ExecSqlCommand(SQL_UPDATE_PRODUCTDESCRIPTION, parms);
        }

        public static void DeleteProductDescription(int ProductSysNo)
        {
            SqlParameter[] parms = new SqlParameter[]{
                new SqlParameter("@ProductSysNo", SqlDbType.Int)
            };
            parms[0].Value = ProductSysNo;

            DataAccessFacade.ExecSqlCommand(SQL_DELETE_PRODUCTDESCRIPTION, parms);
        }
    }

}
